package util;

import java.sql.*;

public class BaseDB {

    static final String DRIVER = "com.mysql.jdbc.Driver";
    static final String URL = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8";
    static final String DBNAME = "root";
    static final String DBPASS = "root";

    /**
     * 获得连接
     *
     * @return
     */
    public static Connection getConn() {
        Connection conn = null;
        try {
            Class.forName(DRIVER);
            conn = DriverManager.getConnection(URL, DBNAME, DBPASS);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }


    /**
     * 通用的增删改方法
     *
     * @param sql
     * @param params
     * @return
     */
    public static int executeZSG(String sql, Object[] params) {
        int num = 0;
        Connection conn = getConn();
        PreparedStatement pstmt = null;

        try {
            pstmt = conn.prepareStatement(sql);
            setParams(pstmt, params);
            num = pstmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(conn, pstmt, null);
        }
        return num;

    }

    /**
     * 通用的查询
     *
     * @param sql
     */
    public static void executeCha(String sql, Object[] params) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = getConn();
            pstmt = conn.prepareStatement(sql);
            setParams(pstmt, params);
            rs = pstmt.executeQuery();
            ResultSetMetaData rsd = rs.getMetaData();

            int colCount = rsd.getColumnCount();

            while (rs.next()) {
                for (int i = 1; i <= colCount; i++) {
                    System.out.print(rs.getString(i) + "\t");
                }
                System.out.println();

            }


        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    /**
     * 动态绑定参数
     *
     * @param pstmt
     * @param params
     */
    public static void setParams(PreparedStatement pstmt, Object[] params) {
        if (params == null)
            return;
        try {
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }


    }


    /**
     * 关闭数据库连接
     *
     * @param conn
     * @param st
     * @param rs
     */
    public static void closeAll(Connection conn, Statement st, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (st != null) {
                st.close();
            }
            if (conn != null) {
                conn.close();
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


}
